﻿using System;
using System.Text;
using System.Collections.Generic;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data;
using DotNetNuke.Modules.Help;
using DotNetNuke.Modules.Help.Tests.Mocks;
using System.Transactions;
using System.Data.SqlClient;

namespace DotNetNuke.Modules.Help.Tests
{
    /// <summary>
    /// Summary description for SqlDataProviderFixture
    /// </summary>
    [TestClass]
    public class SqlDataProviderFixture
    {
        readonly SqlDataProvider sqlDataProvider = new SqlDataProvider();
        public SqlDataProviderFixture()
        {
            sqlDataProvider.ConnectionString = "Server=(local);Database=DotNetNuke_Help;uid=HelpTDD;pwd=HelpTDD;";
            sqlDataProvider.ObjectQualifier = "dnn_";
            sqlDataProvider.DatabaseOwner = "dbo.";
        }

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext { get; set; }

        private static string GetCategoryList(IEnumerable<int> categories)
        {
            var categoryList = new StringBuilder();
            categoryList.Append("<Categorys>");
            foreach (int category in categories)
            {
                categoryList.Append(string.Format("<id>{0}</id>", category));
            }
            categoryList.Append("</Categorys>");
            return categoryList.ToString();
        }

        private string AddTestCategories(int numberOfVisibleCategories, int numberOfNonVisibleCategories)
        {
            var categories = new List<int>();
            for (int i = 0; i < numberOfVisibleCategories; i++)
            {
                int categoryId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(categoryId > 0);
                categories.Add(categoryId);
            }
            for (int i = 0; i < numberOfNonVisibleCategories; i++)
            {
                int categoryId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, false, MockHelper.createdByUser);
                Assert.IsTrue(categoryId > 0);
                categories.Add(categoryId);
            }
            return GetCategoryList(categories);
        }

        private void CheckCategoryFields(int moduleId, int itemId, bool updated)
        {
            string categoryTitle = "";
            string categoryDescription = "";
            int? categoryImage = null;
            string categoryKeyWords = "";
            int categoryViewOrder = 1;
            int categoryLevel = 1;
            int categoryParent = 1;
            bool categoryVisible = true;
            IDataReader dataReader = sqlDataProvider.GetCategory(moduleId, itemId);
            if (dataReader.Read())
            {
                categoryTitle = (string)dataReader["Name"];
                categoryDescription = (string)dataReader["Description"];
                categoryImage = (int)dataReader["Image"];
                categoryKeyWords = (string)dataReader["KeyWords"];
                categoryViewOrder = (int)dataReader["ViewOrder"];
                categoryLevel = (int)dataReader["Level"];
                categoryParent = (int)dataReader["Parent"];
                categoryVisible = (bool)dataReader["Visible"];
            }
            dataReader.Close();
            if (updated)
            {
                Assert.AreEqual(MockHelper.categoryTitleUpdated, categoryTitle);
                Assert.AreEqual(MockHelper.categoryDescriptionUpdated, categoryDescription);
                Assert.AreEqual(MockHelper.categoryImageUpdated, categoryImage);
                Assert.AreEqual(MockHelper.categoryKeyWordsUpdated, categoryKeyWords);
                Assert.AreEqual(MockHelper.categoryViewOrderUpdated, categoryViewOrder);
                Assert.AreEqual(MockHelper.categoryLevelUpdated, categoryLevel);
                Assert.AreEqual(MockHelper.categoryParentUpdated, categoryParent);
                Assert.AreEqual(MockHelper.categoryVisibleUpdated, categoryVisible);
            }
            else
            {
                Assert.AreEqual(MockHelper.categoryTitle, categoryTitle);
                Assert.AreEqual(MockHelper.categoryDescription, categoryDescription);
                Assert.AreEqual(MockHelper.categoryImage, categoryImage);
                Assert.AreEqual(MockHelper.categoryKeyWords, categoryKeyWords);
                Assert.AreEqual(MockHelper.categoryViewOrder, categoryViewOrder);
                Assert.AreEqual(MockHelper.categoryLevel, categoryLevel);
                Assert.AreEqual(MockHelper.categoryParent, categoryParent);
                Assert.AreEqual(MockHelper.categoryVisible, categoryVisible);
            }
        }

        private void CheckTutorialFields(int moduleId, int itemId, bool updated)
        {
            string productImage = "";
            string title = "";
            bool featured = true;
            string keyWords = "";
            string downloadName = "";
            string shortDesc = "";
            bool archive = true;
            string description = "";

            IDataReader dataReader = sqlDataProvider.GetTutorial(moduleId, itemId);
            if (dataReader.Read())
            {
                productImage = (string)dataReader["ProductImage"];
                title = (string)dataReader["Title"];
                featured = (bool)dataReader["Featured"];
                keyWords = (string)dataReader["KeyWords"];
                downloadName = (string)dataReader["DownloadName"];
                shortDesc = (string)dataReader["ShortDesc"];
                archive = (bool)dataReader["Archive"];
                description = (string)dataReader["Description"];
            }
            dataReader.Close();
            if (updated)
            {
                Assert.AreEqual(MockHelper.ProductImageUpdated, productImage);
                Assert.AreEqual(MockHelper.TitleUpdated, title);
                Assert.AreEqual(MockHelper.FeaturedUpdated, featured);
                Assert.AreEqual(MockHelper.KeyWordsUpdated, keyWords);
                Assert.AreEqual(MockHelper.DownloadNameUpdated, downloadName);
                Assert.AreEqual(MockHelper.ShortDescUpdated, shortDesc);
                Assert.AreEqual(MockHelper.ArchiveUpdated, archive);
                Assert.AreEqual(MockHelper.DescriptionUpdated, description);
            }
            else
            {
                Assert.AreEqual(MockHelper.ProductImage, productImage);
                Assert.AreEqual(MockHelper.Title, title);
                Assert.AreEqual(MockHelper.Featured, featured);
                Assert.AreEqual(MockHelper.KeyWords, keyWords);
                Assert.AreEqual(MockHelper.DownloadName, downloadName);
                Assert.AreEqual(MockHelper.ShortDesc, shortDesc);
                Assert.AreEqual(MockHelper.Archive, archive);
                Assert.AreEqual(MockHelper.Description, description);
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        #region Category Tests
        [TestMethod]
        [Description("Adds and retrieves a Category record in the database from imported XML")]
        [Owner("Philip Beadle")]
        public void AddCategorySuccess()
        {
            using (new TransactionScope())
            {
                int itemId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);
                CheckCategoryFields(MockHelper.CategoryModuleId, itemId, false);
            }
        }

        [TestMethod]
        [Description("Adds and retrieves a Category record in the database from the XML import")]
        [Owner("Philip Beadle")]
        public void AddCategoryFromImportSuccess()
        {
            Guid categoryGuid = Guid.NewGuid();
            using (new TransactionScope())
            {
                int itemId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, categoryGuid, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);
                CheckCategoryFields(MockHelper.CategoryModuleId, itemId, false);
            }
        }

        [TestMethod]
        [Description("Adds and retrieves a Category record in the database")]
        [Owner("Philip Beadle")]
        public void UpdateCategoryFromImportSuccess()
        {
            int updatetemId = -1;

            using (new TransactionScope())
            {
                int itemId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);

                IDataReader dataReader = sqlDataProvider.GetCategory(MockHelper.CategoryModuleId, itemId);
                if (dataReader.Read())
                {
                    var categoryGuid = (Guid)dataReader["CategoryGuid"];
                    updatetemId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, categoryGuid, MockHelper.categoryTitleUpdated, MockHelper.categoryDescriptionUpdated, MockHelper.categoryImageUpdated, MockHelper.categoryKeyWordsUpdated, MockHelper.categoryViewOrderUpdated, MockHelper.categoryLevelUpdated, MockHelper.categoryParentUpdated, MockHelper.categoryVisibleUpdated, MockHelper.createdByUser);
                }
                dataReader.Close();
                Assert.AreEqual(itemId, updatetemId);
                CheckCategoryFields(MockHelper.CategoryModuleId, itemId, true);
            }
        }

        [TestMethod]
        [Description("Adds, updates and retrieves a Category record in the database")]
        [Owner("Philip Beadle")]
        public void UpdateCategorySuccess()
        {
            using (new TransactionScope())
            {
                int itemId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);

                sqlDataProvider.UpdateCategory(MockHelper.CategoryModuleId, itemId, MockHelper.categoryTitleUpdated, MockHelper.categoryDescriptionUpdated, MockHelper.categoryImageUpdated, MockHelper.categoryKeyWordsUpdated, MockHelper.categoryViewOrderUpdated, MockHelper.categoryLevelUpdated, MockHelper.categoryParentUpdated, MockHelper.categoryVisibleUpdated, MockHelper.createdByUser);
                CheckCategoryFields(MockHelper.CategoryModuleId, itemId, true);
            }
        }

        [TestMethod]
        [Description("Adds and retrieves a Category record in the database")]
        [Owner("Philip Beadle")]
        public void GetCategoriesByTutorialId()
        {
            using (new TransactionScope())
            {
                string categories = AddTestCategories(5, 2);
                int itemId = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                IDataReader dataReader = sqlDataProvider.GetCategories(MockHelper.ModuleId, itemId);
                int items = 0;
                while (dataReader.Read())
                {
                    items++;
                }
                dataReader.Close();
                Assert.AreEqual(7, items);
            }
        }

        [TestMethod]
        [Description("Adds a list of categories, mixed active and non active and retrieves a list of all Categories record in the database")]
        [Owner("Philip Beadle")]
        public void GetCategoriesByModuleId()
        {
            using (new TransactionScope())
            {
                AddTestCategories(5, 2);
                var dataReader = sqlDataProvider.GetCategories(MockHelper.CategoryModuleId, false);
                var items = 0;
                while (dataReader.Read())
                {
                    items++;
                }
                dataReader.Close();
                Assert.AreEqual(7, items);
            }
        }

        [TestMethod]
        [Description("Adds a list of categories, mixed active and non active and retrieves a list of active Categories record in the database")]
        [Owner("Philip Beadle")]
        public void GetActiveCategoriesByModuleId()
        {
            using (new TransactionScope())
            {
                AddTestCategories(5, 2);
                var dataReader = sqlDataProvider.GetCategories(MockHelper.CategoryModuleId, true);
                var items = 0;
                while (dataReader.Read())
                {
                    items++;
                }
                dataReader.Close();
                Assert.AreEqual(5, items);
            }
        }

        [TestMethod]
        [Description("Adds a list of categories, mixed parentids and visibility and retrieves a list of Categories based on parentid")]
        [Owner("Philip Beadle")]
        public void GetChildCategories()
        {
            using (new TransactionScope())
            {
                //Add 3 with parent = 1
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, false, MockHelper.createdByUser);

                //Add 3 with parent = 2
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, 2, MockHelper.categoryVisible, MockHelper.createdByUser);
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, 2, MockHelper.categoryVisible, MockHelper.createdByUser);
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, 2, false, MockHelper.createdByUser);
                
                //Add 3 with parent = 3
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, 3, MockHelper.categoryVisible, MockHelper.createdByUser);
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, 3, MockHelper.categoryVisible, MockHelper.createdByUser);
                sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, 3, false, MockHelper.createdByUser);

                var dataReader = sqlDataProvider.GetChildCategories(MockHelper.CategoryModuleId, 1, true);
                var items = 0;
                while (dataReader.Read())
                {
                    items++;
                }
                dataReader.Close();
                Assert.AreEqual(2, items);

                var dataReader2 = sqlDataProvider.GetChildCategories(MockHelper.CategoryModuleId, 1, false);
                items = 0;
                while (dataReader2.Read())
                {
                    items++;
                }
                dataReader2.Close();
                Assert.AreEqual(3, items);

                var dataReader3 = sqlDataProvider.GetChildCategories(MockHelper.CategoryModuleId, 2, true);
                items = 0;
                while (dataReader3.Read())
                {
                    items++;
                }
                dataReader3.Close();
                Assert.AreEqual(2, items);

                var dataReader4 = sqlDataProvider.GetChildCategories(MockHelper.CategoryModuleId, 2, false);
                items = 0;
                while (dataReader4.Read())
                {
                    items++;
                }
                dataReader4.Close();
                Assert.AreEqual(3, items);

                var dataReader5 = sqlDataProvider.GetChildCategories(MockHelper.CategoryModuleId, 3, true);
                items = 0;
                while (dataReader5.Read())
                {
                    items++;
                }
                dataReader5.Close();
                Assert.AreEqual(2, items);

                var dataReader6 = sqlDataProvider.GetChildCategories(MockHelper.CategoryModuleId, 3, false);
                items = 0;
                while (dataReader6.Read())
                {
                    items++;
                }
                dataReader6.Close();
                Assert.AreEqual(3, items);
            }
        }


        [TestMethod]
        [Description("Adds and Deletes a Category record in the database")]
        [Owner("Philip Beadle")]
        public void DeleteCategorySuccess()
        {
            using (new TransactionScope())
            {
                int itemId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);
                CheckCategoryFields(MockHelper.CategoryModuleId, itemId, false);
                sqlDataProvider.DeleteCategory(MockHelper.CategoryModuleId, itemId);
                IDataReader dataReader = sqlDataProvider.GetCategory(MockHelper.CategoryModuleId, itemId);
                if (dataReader.Read())
                {
                    var deleted = (bool)dataReader["Deleted"];
                    Assert.IsTrue(deleted);
                }
                dataReader.Close();
            }
        }

        #endregion

        #region Tutorial Tests
        [TestMethod]
        [Description("Adds and retrieves a Tutorial record in the database")]
        [Owner("Philip Beadle")]
        public void AddTutorialSuccess()
        {
            using (new TransactionScope())
            {
                string categories = AddTestCategories(2, 0);
                int itemId = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);
                CheckTutorialFields(MockHelper.ModuleId, itemId, false);
            }
        }

        [TestMethod]
        [Description("Tries to add a Tutorial record with categories that dont exist, causes a SQL Exception")]
        [Owner("Philip Beadle")]
        [ExpectedException(typeof(SqlException))]
        public void AddTutorialFailNonExistentCategories()
        {
            using (new TransactionScope())
            {
                sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, MockHelper.CategoryListNonExistent, MockHelper.createdByUser);
            }
        }

        [TestMethod]
        [Description("Updates a Tutorial record from the imported XML, Guids match")]
        [Owner("Philip Beadle")]
        public void UpdateTutorialFromImportSuccess()
        {
            using (new TransactionScope())
            {
                var categories = AddTestCategories(3, 0);

                var itemId = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);

                var dataReader = sqlDataProvider.GetTutorial(MockHelper.ModuleId, itemId);
                if (dataReader.Read())
                {
                    var tutorialGuid = (Guid)dataReader["TutorialGuid"];
                    //This sproc will only add if the guid cant be matched.
                    var updateItemId = sqlDataProvider.AddTutorial(MockHelper.ModuleId, tutorialGuid, MockHelper.TitleUpdated, MockHelper.ProductImageUpdated, MockHelper.FeaturedUpdated, MockHelper.KeyWordsUpdated, MockHelper.DownloadNameUpdated, MockHelper.ShortDescUpdated, MockHelper.ArchiveUpdated, MockHelper.DescriptionUpdated, categories, MockHelper.createdByUser);
                    Assert.AreEqual(updateItemId, itemId);
                }
                else
                {
                    Assert.Fail();
                }
                dataReader.Close();
                CheckTutorialFields(MockHelper.ModuleId, itemId, true);
            }
        }

        [TestMethod]
        [Description("Adds a Tutorial record from the imported XML, Guids do not match")]
        [Owner("Philip Beadle")]
        public void AddTutorialFromImportSuccess()
        {
            using (new TransactionScope())
            {
                var records = 0;
                var records2 = 0;
                var dataReader = sqlDataProvider.GetTutorials(MockHelper.ModuleId);
                while (dataReader.Read())
                {
                    records += 1;
                }
                dataReader.Close();
                var categories = AddTestCategories(3, 0);

                var tutorialGuid = Guid.NewGuid();
                var itemId = sqlDataProvider.AddTutorial(MockHelper.ModuleId, tutorialGuid, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);

                var dataReader2 = sqlDataProvider.GetTutorials(MockHelper.ModuleId);
                while (dataReader2.Read())
                {
                    records2 += 1;
                }
                dataReader2.Close();
                Assert.AreEqual(records + 1, records2);
                CheckTutorialFields(MockHelper.ModuleId, itemId, false);

            }
        }

        [TestMethod]
        [Description("Adds 2 Tutorial records and then checks to see if 2 were added.")]
        [Owner("Philip Beadle")]
        public void GetTutorialsByModuleId()
        {
            using (new TransactionScope())
            {
                int records = 0;
                int records2 = 0;
                IDataReader dataReader = sqlDataProvider.GetTutorials(MockHelper.ModuleId);
                while (dataReader.Read())
                {
                    records += 1;
                }
                dataReader.Close();
                string categories = AddTestCategories(3, 0);
                int itemId1 = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                int itemId2 = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId1 > 0);
                Assert.IsTrue(itemId2 > 0);

                IDataReader dataReader2 = sqlDataProvider.GetTutorials(MockHelper.ModuleId);
                while (dataReader2.Read())
                {
                    records2 += 1;
                }
                dataReader2.Close();
                Assert.AreEqual(records + 2, records2);
            }
        }

        [TestMethod]
        [Description("Adds 2 Tutorial records and then checks to see if 2 were added.")]
        [Owner("Philip Beadle")]
        public void GetTutorialsByCategoryId()
        {
            using (new TransactionScope())
            {
                int records = 0;
                int records2 = 0;
                List<int> categoriesList = new List<int>();
                int categoryId = sqlDataProvider.AddCategory(MockHelper.CategoryModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(categoryId > 0);
                categoriesList.Add(categoryId);
                string categories = GetCategoryList(categoriesList);
                IDataReader dataReader = sqlDataProvider.GetTutorials(MockHelper.ModuleId, categoryId, false);
                while (dataReader.Read())
                {
                    records += 1;
                }
                dataReader.Close();
                //Both are added to the cat added above
                int itemId1 = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                int itemId2 = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId1 > 0);
                Assert.IsTrue(itemId2 > 0);

                IDataReader dataReader2 = sqlDataProvider.GetTutorials(MockHelper.ModuleId, categoryId, false);
                while (dataReader2.Read())
                {
                    records2 += 1;
                }
                dataReader2.Close();
                Assert.AreEqual(records + 2, records2);
            }
        }

        [TestMethod]
        [Description("Adds 2 Tutorial records and then checks to see if 2 were added.")]
        [Owner("Philip Beadle")]
        public void GetTutorialsByCategoryIdAlphabetical()
        {
            using (new TransactionScope())
            {
                List<int> categoriesList = new List<int>();
                int categoryId = sqlDataProvider.AddCategory(MockHelper.ModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(categoryId > 0);
                categoriesList.Add(categoryId);
                string categories = GetCategoryList(categoriesList);
                //Add 2 records in reverse alphabetical order
                int itemId1 = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title2, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                int itemId2 = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId1 > 0);
                Assert.IsTrue(itemId2 > 0);
                IDataReader dataReader = sqlDataProvider.GetTutorials(MockHelper.ModuleId, categoryId, true);
                dataReader.Read();
                string title1 = (string)dataReader["Title"];
                dataReader.Read();
                string title2 = (string)dataReader["Title"];
                Assert.AreEqual(title1, MockHelper.Title);
                Assert.AreEqual(title2, MockHelper.Title2);
                dataReader.Close();
            }
        }

        [TestMethod]
        [Description("Adds 2 Tutorial records and then checks to see if 2 were added.")]
        [Owner("Philip Beadle")]
        public void UpdateTutorialViewOrder()
        {
            using (new TransactionScope())
            {
                List<int> categoriesList = new List<int>();
                int categoryId = sqlDataProvider.AddCategory(MockHelper.ModuleId, MockHelper.categoryTitle, MockHelper.categoryDescription, MockHelper.categoryImage, MockHelper.categoryKeyWords, MockHelper.categoryViewOrder, MockHelper.categoryLevel, MockHelper.categoryParent, MockHelper.categoryVisible, MockHelper.createdByUser);
                Assert.IsTrue(categoryId > 0);
                categoriesList.Add(categoryId);
                string categories = GetCategoryList(categoriesList);

                //Add 2 records in reverse alphabetical order
                int itemId1 = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title2, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId1 > 0);
                IDataReader dataReader = sqlDataProvider.GetTutorials(MockHelper.ModuleId, categoryId, false);
                dataReader.Read();
                int viewOrder1 = (int)dataReader["ViewOrder"];
                Assert.AreEqual(viewOrder1, 99);
                dataReader.Close();

                sqlDataProvider.UpdateTutorial(itemId1, categoryId, 0);
                IDataReader dataReader2 = sqlDataProvider.GetTutorials(MockHelper.ModuleId, categoryId, false);
                dataReader2.Read();
                int viewOrder2 = (int)dataReader2["ViewOrder"];
                Assert.AreEqual(viewOrder2, 0);
                dataReader2.Close();
            }
        }

        [TestMethod]
        [Description("Adds, updates and retrieves a Tutorial record")]
        [Owner("Philip Beadle")]
        public void UpdateTutorialNotAssociatedCategories()
        {
            using (new TransactionScope())
            {
                string categories = AddTestCategories(3, 0);

                int itemId = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);

                sqlDataProvider.UpdateTutorial(MockHelper.ModuleId, itemId, MockHelper.TitleUpdated, MockHelper.ProductImageUpdated, MockHelper.FeaturedUpdated, MockHelper.KeyWordsUpdated, MockHelper.DownloadNameUpdated, MockHelper.ShortDescUpdated, MockHelper.ArchiveUpdated, MockHelper.DescriptionUpdated, categories, MockHelper.createdByUser);

                CheckTutorialFields(MockHelper.ModuleId, itemId, true);
            }
        }

        [TestMethod]
        [Description("Tries to add a Tutorial record with categories that dont exist, causes a SQL Exception")]
        [Owner("Philip Beadle")]
        [ExpectedException(typeof(SqlException))]
        public void UpdateTutorialFailSecurityViolation()
        {
            using (new TransactionScope())
            {
                int itemId = sqlDataProvider.AddTutorial(MockHelper.ModuleIdFail, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, MockHelper.CategoryListNonExistent, MockHelper.createdByUser);
            }
        }

        [TestMethod]
        [Description("Adds, retrieves and then deletes a Tutorial record in the database")]
        [Owner("Philip Beadle")]
        public void DeleteTutorialSuccess()
        {
            string productImage = "";
            using (new TransactionScope())
            {
                string categories = AddTestCategories(3, 0);

                int itemId = sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                Assert.IsTrue(itemId > 0);

                IDataReader dataReader = sqlDataProvider.GetTutorial(MockHelper.ModuleId, itemId);
                if (dataReader.Read())
                {
                    productImage = (string)dataReader["ProductImage"];
                }
                dataReader.Close();
                Assert.AreEqual(MockHelper.ProductImage, productImage);

                sqlDataProvider.DeleteTutorial(MockHelper.ModuleId, itemId);
                IDataReader dataReader2 = sqlDataProvider.GetTutorial(MockHelper.ModuleId, itemId);
                if (dataReader2.Read())
                {
                    Assert.Fail();
                }
                dataReader2.Close();
            }
        }

        [TestMethod]
        [Description("Adds and retrieves a Tutorial record in the database")]
        [Owner("Philip Beadle")]
        public void SearchTutorials()
        {
            using (new TransactionScope())
            {
                int records = 0;
                string categories = AddTestCategories(2, 0);
                sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title2, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords2, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc2, MockHelper.Archive, MockHelper.Description, categories, MockHelper.createdByUser);
                sqlDataProvider.AddTutorial(MockHelper.ModuleId, MockHelper.Title, MockHelper.ProductImage, MockHelper.Featured, MockHelper.KeyWords, MockHelper.DownloadName, MockHelper.ShortDesc, MockHelper.Archive, MockHelper.Description2, categories, MockHelper.createdByUser);

                IDataReader dataReader = sqlDataProvider.SearchTutorials("Z", MockHelper.CategoryModuleId);
                while (dataReader.Read())
                {
                    records++;
                }
                dataReader.Close();
                Assert.AreEqual(4, records);
            }
        }

        #endregion
    }
}
